/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import model.PegawaiBean;

/**
 *
 * @author elfatah
 */
public class DBPegawai extends DBClass {

    public void UpdatePegawai(PegawaiBean bb, String id) throws SQLException, ClassNotFoundException {

        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("UPDATE `pegawai` SET `NAMA`=?,`PASSWORD`=md5(?),`ID_BAGIAN`=?,`ALAMAT`=?,`TELP`=?,`ID_GOLONGAN`=? WHERE `PEGAWAI_ID`=?");
        //set values to prepared statement object by getting values from bean object
        pstmt.setString(1, bb.getNama());
        pstmt.setString(2, bb.getPassword());
        //   pstmt.setString(3, bb.getRole());
        pstmt.setString(3, bb.getIdBagian());
        pstmt.setString(4, bb.getAlamat());
        pstmt.setString(5, bb.getTelp());
        pstmt.setString(6, bb.getGajiPegawai());
        pstmt.setString(7, id);
        pstmt.executeUpdate();

    }

    public void deletePegawai(String uname) throws SQLException, ClassNotFoundException {
        //here we will write code to get a single record from database
        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("delete from pegawai where PEGAWAI_ID=?");
        pstmt.setString(1, uname);
        pstmt.executeUpdate();
    }

    public List getPegawaiDetails() throws SQLException, ClassNotFoundException {

        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("SELECT pegawai.PEGAWAI_ID, pegawai.NAMA,bagian.NAMA, bagian.NAMA,pegawai.ALAMAT,pegawai.TELP,golongan.GAJI FROM pegawai,bagian,golongan WHERE pegawai.ID_BAGIAN = bagian.BAGIAN_ID and pegawai.ID_GOLONGAN = golongan.GOLONGAN_ID");
        ResultSet rs = pstmt.executeQuery();
        List list = new ArrayList();
        while (rs.next()) {
            PegawaiBean ubean = new PegawaiBean();
            ubean.setPegawaiId(rs.getString(1));
            ubean.setNama(rs.getString(2));
            // ubean.setRole(rs.getString(3));
            ubean.setIdBagian(rs.getString(4));
            ubean.setAlamat(rs.getString(5));
            ubean.setTelp(rs.getString(6));
            ubean.setGajiPegawai(rs.getString(7));

            list.add(ubean);

        }
        return list;
    }

    public void insertPegawai(PegawaiBean bb) throws SQLException, ClassNotFoundException {
        try {
            Connection con = createConnection();
            PreparedStatement pstmt = con.prepareStatement("INSERT INTO `pegawai`(`USERNAME`,`NAMA`, `PASSWORD`, `ID_BAGIAN`, `ALAMAT`, `TELP`, `ID_GOLONGAN`) VALUES (?,?,md5(?),?,?,?,?)");
            //set values to prepared statement object by getting values from bean object
            pstmt.setString(1, bb.getUsername());
            pstmt.setString(2, bb.getNama());

            pstmt.setString(3, bb.getPassword());
            // pstmt.setString(3, bb.getRole());
            pstmt.setString(4, bb.getIdBagian());
            pstmt.setString(5, bb.getAlamat());
            pstmt.setString(6, bb.getTelp());
            pstmt.setString(7, bb.getGajiPegawai());

             pstmt.executeUpdate();
            System.out.println("testing");
            
        } catch (Exception e) {
            System.out.println(e);
            
        }
    }

    public PegawaiBean getPegawaiDetails(String id) throws SQLException, ClassNotFoundException {
        //here we will write code to get a single record from database
        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("SELECT pegawai.PEGAWAI_ID, pegawai.NAMA,pegawai.PASSWORD,bagian.BAGIAN_ID, bagian.NAMA,pegawai.ALAMAT,pegawai.TELP,golongan.GOLONGAN_ID,golongan.GAJI FROM pegawai,bagian,golongan WHERE pegawai.ID_BAGIAN = bagian.BAGIAN_ID and pegawai.ID_GOLONGAN = golongan.GOLONGAN_ID and pegawai.PEGAWAI_ID=?");
        pstmt.setString(1, id);
        ResultSet rs = pstmt.executeQuery();

        PegawaiBean ubean = new PegawaiBean();
        while (rs.next()) {
            ubean.setPegawaiId(rs.getString(1));
            ubean.setNama(rs.getString(2));
            ubean.setPassword(rs.getString(3));
            // ubean.setRole(rs.getString(4));
            ubean.setIdBagian(rs.getString(4));
            ubean.setNamaBagian("Bagian " + rs.getString(5));
            ubean.setAlamat(rs.getString(6));
            ubean.setTelp(rs.getString(7));
            ubean.setIdGolongan(rs.getString(8));
            ubean.setGajiPegawai(rs.getString(9));
        }
        ubean.setAction("Update");
        return ubean;
    }

}
